﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using SapB1WebPOS.DAL;
using SapB1WebPOS.Utils;
using System.Text;

namespace SapB1WebPOS.DAL
{
    public class CommonDAO
    {
        /// <summary>
        /// Get All SalesEmployee
        /// </summary>
        /// <param name="companyDB"></param>
        /// <param name="keySearch"></param>
        /// <returns></returns>
        public DataSet GetAllSalesEmployee(string companyDB, string keySearch)
        {
            try
            {
                DataSet ds = new DataSet("OSLP");
                SQLConnection connect = new SQLConnection(companyDB);

                if (keySearch.Length > 0)
                {
                    List<object> lstParamValue = new List<object>();
                    lstParamValue.Add(keySearch);
                    ds = connect.Excute_Query("SELECT * FROM OSLP WHERE SplName LIKE '%' + @Param1 + '%'", lstParamValue);
                }
                else
                {
                    ds = connect.Excute_Query("SELECT * FROM OSLP", null);
                }
                return ds;
            }
            catch (Exception ex)
            {
                Log.WriteException(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType, "SystemError", ex);
                throw ex;
            }
        }
        /// <summary>
        /// Get Employee Master Data
        /// </summary>
        /// <param name="companyDB"></param>
        /// <returns></returns>
        public DataSet GetAllSystemEmployee(string companyDB, string keySearch)
        {
            try
            {
                DataSet ds = new DataSet("OHEM");
                SQLConnection connect = new SQLConnection(companyDB);

                if (keySearch.Length > 0)
                {
                    List<object> lstParamValue = new List<object>();
                    lstParamValue.Add(keySearch);
                    ds = connect.Excute_Query("SELECT * FROM OHEM WHERE FirstName LIKE '%' + @Param1 + '%' OR LastName LIKE '%' + @Param1 + '%' OR MiddleName LIKE '%' + @Param1 + '%'", lstParamValue);
                }
                else
                {
                    ds = connect.Excute_Query("SELECT * FROM OHEM", null);
                }
                return ds;
            }
            catch (Exception ex)
            {
                Log.WriteException(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType, "SystemError", ex);
                throw ex;
            }
        }
        /// <summary>
        /// Get Users
        /// </summary>
        /// <param name="companyDB"></param>
        /// <returns></returns>
        public DataSet GetAllSystemUser(string companyDB, string keySearch)
        {
            try
            {
                DataSet ds = new DataSet("OUSR");
                SQLConnection connect = new SQLConnection(companyDB);
                if (keySearch.Length > 0)
                {
                    List<object> lstParamValue = new List<object>();
                    lstParamValue.Add(keySearch);
                    ds = connect.Excute_Query("SELECT * FROM OUSR WHERE USER_CODE LIKE '%' + @Param1 + '%' OR U_NAME LIKE '%' + @Param1 + '%'", lstParamValue);
                }
                else
                {
                    ds = connect.Excute_Query("SELECT * FROM OUSR", null);
                }

                return ds;
            }
            catch (Exception ex)
            {
                Log.WriteException(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType, "SystemError", ex);
                throw ex;
            }
        }

        /// <summary>
        /// Get SAP Display Setting
        /// </summary>
        /// <param name="dbType"></param>
        /// <param name="ID_Dealer"></param>
        /// <returns></returns>
        public DataSet GetDisplaySetting(string companyDB)
        {
            try
            {
                DataSet ds = new DataSet("OADM");
                SQLConnection connect = new SQLConnection(companyDB);
                ds = connect.Excute_Query("SELECT CompnyName,DecSep,ThousSep,SumDec,PriceDec,QtyDec,PercentDec,RateDec,DateFormat,DateSep FROM OADM", null);
                return ds;
            }
            catch (Exception ex)
            {
                Log.WriteException(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType, "SystemError", ex);
                throw ex;
            }
        }
        /// <summary>
        /// Get Payment Account
        /// </summary>
        /// <param name="companyDB"></param>
        /// <param name="taxDate"></param>
        /// <returns></returns>
        public DataSet GetPaymentAccount(string companyDB, DateTime taxDate)
        {
            try
            {
                DataSet dt = new DataSet("OACP");
                SQLConnection connect = new SQLConnection(companyDB);
                List<object> lstParamValue = new List<object>();
                lstParamValue.Add(taxDate);
                dt = connect.Excute_Query("SELECT linkact_3 CashAccount,LinkAct_12 BankTransferAccount FROM OACP WHERE [YEAR]= YEAR(@Param1)", lstParamValue);
                return dt;
            }
            catch (Exception ex)
            {
                Log.WriteException(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType, "SystemError", ex);
                throw ex;
            }
        }

        public DataSet GetObjByBarcode(string companyDB, string barcode)
        {
            List<object> lstParamValue = new List<object>();
            lstParamValue.Add(barcode);
            SQLConnection connect = new SQLConnection(companyDB);

            StringBuilder query = new StringBuilder();
            DataSet dt = new DataSet();

            //------------------1. Customer Code-------------
            query.Append("SELECT ObjType,CardCode ObjCode, CardName ObjName FROM OCRD WHERE Cardcode LIKE '%'+@Param1+'%'");
            dt = connect.Excute_Query(query.ToString(), lstParamValue);
            if (dt != null && dt.Tables.Count > 0 && dt.Tables[0].Rows.Count > 0)
            {
                return dt;
            }

            //	------------------2. Item Code-------------
            query = new StringBuilder();
            query.Append("SELECT ObjType,Itemcode ObjCode, ItemName ObjName FROM OITM WHERE itemcode LIKE '%'+@Param1+'%'");
            dt = connect.Excute_Query(query.ToString(), lstParamValue);
            if (dt != null && dt.Tables.Count > 0 && dt.Tables[0].Rows.Count > 0)
            {
                return dt;
            }

            //------------------3. Customer Name-------------
            query = new StringBuilder();
            query.Append("SELECT ObjType,CardCode ObjCode, CardName ObjName FROM OCRD WHERE CardName LIKE '%'+@Param1+'%'");
            dt = connect.Excute_Query(query.ToString(), lstParamValue);
            if (dt != null && dt.Tables.Count > 0 && dt.Tables[0].Rows.Count > 0)
            {
                return dt;
            }

            //------------------4. Item Barcode-------------
            query = new StringBuilder();
            query.Append("SELECT ObjType,Itemcode ObjCode, ItemName ObjName FROM OITM WHERE CodeBars LIKE '%'+@Param1+'%'");
            dt = connect.Excute_Query(query.ToString(), lstParamValue);
            if (dt != null && dt.Tables.Count > 0 && dt.Tables[0].Rows.Count > 0)
            {
                return dt;
            }

            //------------------5. Item Name-------------
            query = new StringBuilder();
            query.Append("SELECT ObjType,Itemcode ObjCode, ItemName ObjName FROM OITM WHERE ItemName LIKE '%'+@Param1+'%'");
            dt = connect.Excute_Query(query.ToString(), lstParamValue);
            if (dt != null && dt.Tables.Count > 0 && dt.Tables[0].Rows.Count > 0)
            {
                return dt;
            }

            //------------------6. Item Frgn Name-------------
            query = new StringBuilder();
            query.Append("SELECT ObjType,Itemcode ObjCode, ItemName ObjName FROM OITM WHERE FrgnName LIKE '%'+@Param1+'%'");
            dt = connect.Excute_Query(query.ToString(), lstParamValue);
            if (dt != null && dt.Tables.Count > 0 && dt.Tables[0].Rows.Count > 0)
            {
                return dt;
            }

            //------------------7. Card Frgn Name-------------
            query = new StringBuilder();
            query.Append("SELECT ObjType,CardCode ObjCode, CardName ObjName FROM OCRD WHERE CardFName LIKE '%'+@Param1+'%'");
            dt = connect.Excute_Query(query.ToString(), lstParamValue);

            if (dt != null && dt.Tables.Count > 0 && dt.Tables[0].Rows.Count > 0)
            {
                return dt;
            }

            return null;
        }

        /// <summary>
        /// Get Payment Type
        /// </summary>
        /// <param name="companyDB"></param>
        /// <returns></returns>
        public DataSet GetPaymentType(string companyDB)
        {
            StringBuilder query = new StringBuilder();

            query.Append("SELECT 'CASH' PaymentType, 'CASH' PaymentCode, 'CASH' PaymentName,'0' Amount, '0' AcctCode");
            query.Append(" UNION ALL ");
            query.Append("SELECT 'BT' PaymentType, 'BT' PaymentCode, 'BANK TRANSFER' PaymentName,'0' Amount,'0' AcctCode");
            query.Append(" UNION ALL ");
            query.Append("SELECT 'CC' PaymentType,convert(nvarchar(100),CreditCard) PaymentCode,CardName  PaymentName,'0' Amount,AcctCode FROM OCRC");

            DataSet dt = new DataSet("PaymentType");
            SQLConnection connect = new SQLConnection(companyDB);
            dt = connect.Excute_Query(query.ToString(), null);

            return dt;
        }
        /// <summary>
        /// Get Tax
        /// </summary>
        /// <returns></returns>
        public DataSet GetOVTG(string companyDB, string category)
        {
            try
            {
                DataSet dt = new DataSet("OVTG");
                SQLConnection connect = new SQLConnection(companyDB);
                List<object> lstParamValue = new List<object>();
                lstParamValue.Add(category);
                dt = connect.Excute_Query("SELECT Code, Name, Rate,Category FROM OVTG WHERE Category = @Param1 ", lstParamValue);
                return dt;
            }
            catch (Exception ex)
            {
                Log.WriteException(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType, "SystemError", ex);
                throw ex;
            }
        }
        /// <summary>
        /// Get Warehouse
        /// </summary>
        /// <returns></returns>
        public DataSet GetOWHS(string companyDB)
        {
            try
            {
                DataSet dt = new DataSet("OWHS");
                SQLConnection connect = new SQLConnection(companyDB);
                dt = connect.Excute_Query("SELECT WhsCode, WhsName FROM OWHS  ", null);
                return dt;
            }
            catch (Exception ex)
            {
                Log.WriteException(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType, "SystemError", ex);
                throw ex;
            }
        }
        /// <summary>
        /// Get Company
        /// </summary>
        /// <returns></returns>
        public DataSet GetAllCompany(string companyDB)
        {
            try
            {
                DataSet dt = new DataSet("SRGC");
                SQLConnection connect = new SQLConnection(companyDB);
                //dt = connect.Excute_Query("SELECT dbname,cmpname FROM [SBO-COMMON]..SRGC", null);
                dt = connect.Excute_Query("SELECT dbname,cmpname FROM SRGC", null);
                return dt;
            }
            catch (Exception ex)
            {
                Log.WriteException(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType, "SystemError", ex);
                throw ex;
            }
        }
    }
}